package com.tjyx.common.util.db;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.tjyx.common.TJYXException;
import com.tjyx.common.util.ReflectUtil;

/**
 * 该公共类用于创建通用的数据库增、删、改、查方法
 * 
 * 时间：2012-3-23
 * 
 * @author zhutianjie
 * @version 1.0
 * 
 */
public class JDBCTemplate {

	private Connection conn;

	private PreparedStatement ps;

	private ResultSet rs;
	
	private RowSelection selection = new RowSelection();

	public RowSelection getSelection() {
		return selection;
	}

	public void setSelection(RowSelection selection) {
		this.selection = selection;
	}

	/**
	 * 带有可变数量参数(?,?,?...)的更新方法，用来完成增、删、改操作
	 * 
	 * @param sql sql查询语句
	 * @param parameters 可变长度参数
	 * @return
	 * @throws TJYXException 
	 */
	public int update(String sql, Object... parameters) throws TJYXException {
		int rows = 0;
		try {
			conn = DBConnector.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < parameters.length; i++) {
				ps.setObject(i + 1, parameters[i]);
			}
			rows = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new TJYXException(e.getMessage());
		} finally {
			DBConnector.clear(conn, ps, rs);
		}
		return rows;
	}

	/**
	 * 带有可变数量参数(?,?,?...)的查询总记录条数
	 * 
	 * @param sql
	 * @param parameters
	 * @return
	 */
	public long  selectCounter(String sql, Object... parameters) {
		long counter = 0;
		conn = DBConnector.getConnection();
		try {
			ps = createPSWithPagination(conn, sql);
			for (int i = 0; i < parameters.length; i++) {
				ps.setObject(i + 1, parameters[i]);
			}
			rs = executeQueryWithPagination(ps);
			while(rs.next()) {
				counter = rs.getLong(1);
			}
			System.out.println("counter" + counter);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return counter;
		
	}
	/**
	 * 带有可变数量参数(?,?,?...)的查询方法
	 * 
	 * @param sql sql查询语句
	 * @param parameters 可变长度参数
	 * @return
	 */
	public Map<String, Object> selectMap(String sql, Object... parameters) {
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			conn = DBConnector.getConnection();
			ps = createPSWithPagination(conn, sql);
			for (int i = 0; i < parameters.length; i++) {
				ps.setObject(i + 1, parameters[i]);
			}
			rs = executeQueryWithPagination(ps);
			if (rs.next()) {
				ResultSetMetaData rsmd = rs.getMetaData();
				int columnCount = rsmd.getColumnCount();
				for (int i = 0; i < columnCount; i++) {
					String key = rsmd.getColumnName(i + 1);
					Object obj = rs.getObject(i + 1);
					map.put(key, obj);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnector.clear(conn, ps, rs);
		}
		return map;
	}

	/**
	 * 带有可变数量参数(?,?,?...)的查询方法，返回多条记录对应的集合
	 * 
	 * @param sql sql查询语句
	 * @param parameters 可变长度参数
	 * @return
	 */
	public List<Map<String, Object>> selectMapList(String sql,
			Object... parameters) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		try {
			conn = DBConnector.getConnection();
			ps = createPSWithPagination(conn, sql);
			for (int i = 0; i < parameters.length; i++) {
				ps.setObject(i + 1, parameters[i]);
			}
			rs = executeQueryWithPagination(ps);
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				ResultSetMetaData rsmd = rs.getMetaData();
				int columnCount = rsmd.getColumnCount();
				for (int i = 1; i <= columnCount; i++) {
					String key = rsmd.getColumnName(i);
					Object obj = rs.getObject(i);
					map.put(key, obj);
					list.add(map);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnector.clear(conn, ps, rs);
		}
		return list;

	}

	/**
	 * 带有可变数量参数(?,?,?...)的查询方法，返回装载多个数据传输类的集合
	 * 
	 * @param <T>
	 * @param sql sql查询语句
	 * @param clazz 数据传输类
	 * @param parameters 可变长度参数
	 * @return
	 * <p>
	 * 备注：需要注意的是,JDBC在查询数据库整型数是,会自动转型成Long,所以数据传输对象的主键id只能设置为Long型
	 * </p>
	 * 
	 */
	public <T> List<T> selectObjectList(String sql, Class<T> clazz,
			Object... parameters) {
		List<T> list = new ArrayList<T>();
		conn = DBConnector.getConnection();
		try {
//			ps = conn.prepareStatement(sql);
			ps = createPSWithPagination(conn, sql);
			for (int i = 0; i < parameters.length; i++) {
				ps.setObject(i + 1, parameters[i]);
			}
			rs = executeQueryWithPagination(ps);
			while (rs.next()) {
				ResultSetMetaData rsmd = rs.getMetaData();
				int columnCount = rsmd.getColumnCount();
				T t = clazz.newInstance();
				for (int i = 1; i <= columnCount; i++) {
					String key = rsmd.getColumnName(i);
					Method setMethod = ReflectUtil.createSetMethod(key, clazz);
					Field field;
					try {
						field = clazz.getDeclaredField(key);
						Class fieldType = field.getType();
						if(fieldType.getName().equals("int")){
							setMethod.invoke(t,(rs.getInt(i)));
						}
						else if(fieldType.getName().equals("java.lang.String")){
							setMethod.invoke(t,(rs.getString(i)));
						}
						else if(fieldType.getName().equals("java.util.Date")){
							System.out.println(fieldType.getName());
							Date date=rs.getDate(i);
							setMethod.invoke(t,date.toString());
						}else{
							setMethod.invoke(t,(rs.getObject(i)));
						}
					} catch (SecurityException e) {
						e.printStackTrace();
					} catch (NoSuchFieldException e) {
						e.printStackTrace();
					}
				}
				list.add(t);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}finally {
			DBConnector.clear(conn, ps, rs);
		}
		return list;
	}
	
	
	/**
	 * 创建已经设置了最大查询行数的PreparedStatement
	 * 
	 * @param conn
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	private PreparedStatement createPSWithPagination(Connection conn,String sql) throws SQLException {
		PreparedStatement ps = conn.prepareStatement(sql);
		RowSelection selection = getSelection();
		if(selection != null && selection.getMaxRows() != null) {
			ps.setMaxRows(selection.getMaxRows());
		}
		return ps;
	}
	
	/**
	 * 创建设置了其实查询位置的ResultSet
	 * @param ps
	 * @return
	 * @throws SQLException
	 */
	private ResultSet executeQueryWithPagination(PreparedStatement ps) throws SQLException {
		ResultSet rs = ps.executeQuery();
		RowSelection selection = getSelection();
		if(selection != null && selection.getFirstRow() != null) {
			rs.absolute(selection.getFirstRow());
		}
		return rs;
	}
}
